from flask import current_app as app,jsonify,make_response
from flask import Blueprint,request
from db import db1
import os
from utils.myfile import myf
from xlrd import open_workbook
bp_company = Blueprint("company",__name__)

# 上传excel，并导入数据库
@bp_company.route('/upload',methods=['POST'])
def upload():
    # 获取文件
    file = request.files['file']
    newname = myf.rename(file.filename)
    # 上传文件
    file.save(os.path.join('./static/files/'+newname))
    # 打开文件读取
    newname = '81df72d56ab44d1584d45946208efbeb.xlsx'
    name = '../static/files/'+newname
    # bk = open_workbook(name,encoding='utf-8')
    sh = bk.sheets()[0]
    nrows = sh.nrows
    ncols = sh.ncols
    for i in range(1,nrows):
        row_date =sh.row_values(i)
        print(row_date[0])
        print(row_date[1])
    # for i in range(2,nrows):
    #     row_date =sh.row_values(i)
    #     for j in range(0,ncols):
    #         print(row_date[j])
    # return 'ok'
    
    # 封装数据写入数据库


# @bp_company.route("/test")
# def test():
#     str = "请求ip:"+request.remote_addr+',请求的url'+request.url+'错误信息'+'\n'
#     app.logger.error(str)
#     return "hello3254626"


from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
import xlsxwriter
import datetime
import mimetypes

@bp_company.route('/download',methods=["GET","POST"])
def download():
     # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    sql = "select * from test limit 0,10"
    res = db1.find_all(sql)
    table.write(0, 0, "编号")
    table.write(0, 1, "名称")
    num = 1
    for i in res:
        table.write(num, 0, i['id'])
        table.write(num, 1, i['name'])
        num += 1
   
    workbook.close()
    filename = quote("资产列表_{}.xls".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xls' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file

import copy
def xtree(data):
    if len(data) <= 0:
        return data
    # for i in data:
    # 先把data封装成字典
    list = []
    dict={}
    for i in data:
        i['label'] = i['name']
        dict[i['id']] = i
    # 二次遍历
    for j in data:
        # 判断父id是否为0
        pid = j['pid']
        if pid==0:
            j['label'] = j['name']
            list.append(j)
        else:
            # 子类
            # 看父类下有没有，如果已经有，直接加入到列表中
            # 如果没有创建恐龙列表
            
            if 'children' not in dict[pid]:
                dict[pid]['children'] = []
            dict[pid]['children'].append(j)
    return list



@bp_company.route('/deptlist')
def deptlist():
    sql = "select * from dept"
    res = db1.find_all(sql)
    list = xtree(res)
    return jsonify({'code':200,'list':list})

# @bp_company.route('zuzhi')
# def zuzhi():
#     id = request.args.get('id')
#     sql = ""


@bp_company.route('/getuserlist')
def getuserlist():
    sql = "select * from users"
    print(sql)
    res = db1.find_all(sql)
    print(res)
    return jsonify({'code':200,'list':res})


# from utils.es import Es
# @bp_company.route('/deptesinsert')
# def method_name():
#     es = Es('testes','test-type')
#     # 插入数据
#     sql = "select * from test limit 0,10"
#     res = db1.find_all(sql)
#     for i in res:
#         es.insert(i)
#     return 'ok'


# @bp_company.route('/getdata')
# def method_name():
#     es = Es('testes','test-type')
#     # query = {'match':{'name':'abc'}}
#     query = {
#         "multi_match":{
#             "query":"abc"
#             "fields":['name':'cotent']
#         }
#     }
#     # 查询数据
#     data = es.find(query)
#     mes = data['hits']['hits']
#     print(mes)
#     # print(data)
#     return 'ok'


